﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_device_config_sel]
	@DEVICE_CONFIG_ID INT = NULL
	,@AUDIT_USER VARCHAR(100) = NULL
	,@AUDIT_DTTM DATETIME = NULL
	,@AUDIT_DTTM_TZ INT = NULL
	,@AUDIT_MOD_USER VARCHAR(100) = NULL
	,@AUDIT_MOD_DTTM DATETIME = NULL
	,@AUDIT_MOD_DTTM_TZ INT = NULL
	,@BUILTIN BIT = NULL
	,@ACTIVE_FLG BIT = NULL
	,@DEVICE_ID INT= NULL
	,@DEVICE_DOMAIN_ID INT = NULL
	,@DEVICE_CONFIG_PROP_ID INT = NULL
	,@DEVICE_CONFIG_PROP_VALUE VARCHAR(MAX) = NULL
	
AS
BEGIN
	SELECT A.[DEVICE_CONFIG_ID]
		  ,A.[AUDIT_USER]
		  ,A.[AUDIT_DTTM]
		  ,A.[AUDIT_DTTM_TZ] 
		  ,A.[AUDIT_MOD_USER]
		  ,A.[AUDIT_MOD_DTTM]
		  ,A.[AUDIT_MOD_DTTM_TZ] 
		  ,A.[BUILTIN]
		  ,A.[WRKFL_ITEM_ID]
		  ,A.[WRKFL_STATUS_ID]
		  ,A.[ACTIVE_FLG]
		  ,A.[SORT_ORDER]
		  ,A.[DEVICE_ID]
		  ,C.[DOMAIN_NAME] AS DEVICE_DOMAIN_NAME 
		  ,B.[DEVICE_ASSET_NAME]
		  ,B.[DEVICE_SERIAL_NUM] 
		  ,B.[DEVICE_ASSET_TAG]
		  ,A.[DEVICE_CONFIG_PROPERTY_ID]
		  ,E.[DEVICE_PROP_TYPE_NAME] AS PNAME
		  ,F.[DEVICE_PROP_TYPE_NAME] AS PVALUE
		  ,A.[DEVICE_CONFIG_PROP_VALUE]
		  ,A.[UTIL_MEASURES_TYPE]
		  ,A.[PARENT_ID]
		  ,A.[AUTOCOLLECT_FLG]
		  ,A.[AUTOCOLLECT_MODULE_ID]
		  ,A.[AUTOCOLLECT_FDTTM]
		  ,A.[AUTOCOLLECT_LDTTM]
		  ,A.[AUTOCOLLECT_AUDIT_USER]
	  FROM [flcr].[DEVICE_CONFIG]  A
	 INNER JOIN [flcr].[DEVICE] B 
		ON	A.[DEVICE_ID] = B.[DEVICE_ID]
	 INNER JOIN [flcr].[DOMAIN] C
		ON	B.[DOMAIN_ID] = C.[DOMAIN_ID]
	 INNER JOIN [flcr].[DEVICE_CONFIG_PROPERTY] D
		ON	A.[DEVICE_CONFIG_PROPERTY_ID] = D.[DEVICE_CONFIG_PROPERTY_ID]
	 INNER JOIN [flcr].[DEVICE_PROPERTY_TYPE] E
		ON	D.[PNAME_DEVICE_PROP_TYPE] = E.[DEVICE_PROP_TYPE]
	 INNER JOIN [flcr].[DEVICE_PROPERTY_TYPE] F
		ON	D.[PVALUE_DEVICE_PROP_TYPE] = F.[DEVICE_PROP_TYPE]
	 WHERE	A.[DEVICE_CONFIG_ID] = COALESCE(@DEVICE_CONFIG_ID, A.[DEVICE_CONFIG_ID])
	   AND	A.[AUDIT_USER] = COALESCE(@AUDIT_USER, A.[AUDIT_USER])
	   AND	A.[AUDIT_DTTM] = COALESCE(@AUDIT_DTTM, A.[AUDIT_DTTM])
	   AND	A.[AUDIT_DTTM_TZ] = COALESCE(@AUDIT_DTTM_TZ, A.[AUDIT_DTTM_TZ])
	   AND	A.[AUDIT_MOD_USER] = COALESCE(@AUDIT_MOD_USER, A.[AUDIT_MOD_USER])
	   AND	A.[AUDIT_MOD_DTTM] = COALESCE(@AUDIT_MOD_DTTM, A.[AUDIT_MOD_DTTM])
	   AND	A.[AUDIT_MOD_DTTM_TZ] = COALESCE(@AUDIT_MOD_DTTM_TZ, A.[AUDIT_MOD_DTTM_TZ])
	   AND	A.[BUILTIN] = COALESCE(@BUILTIN, A.[BUILTIN])	   
	   AND	A.[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG, A.[ACTIVE_FLG])   		
	   AND	A.[DEVICE_ID] = COALESCE(@DEVICE_ID, A.[DEVICE_ID])
	   AND	C.[DOMAIN_ID] = COALESCE(@DEVICE_DOMAIN_ID, C.[DOMAIN_ID])
END